from django.db import models
from django.utils import timezone
from django.db import models, transaction # 添加 transaction 导入
from django.db.models import Count
from django.db import connection
import re
import logging

logger = logging.getLogger(__name__)

class Movie(models.Model):
    title = models.CharField('电影名称', max_length=200, db_index=True)
    original_title = models.CharField('原始标题', max_length=200, blank=True)
    rank = models.IntegerField('排名', null=True)
    director = models.CharField('导演', max_length=200, null=True, blank=True)
    actors = models.TextField('演员', null=True, blank=True)
    genre = models.CharField('类型', max_length=100, null=True, blank=True)
    intro = models.TextField('简介', null=True, blank=True)
    rating = models.FloatField('评分', null=True, db_index=True)
    year = models.IntegerField('年份', null=True, db_index=True)
    country = models.CharField('国家', max_length=100, null=True, blank=True)
    # 删除重复的 type 字段，使用 genre 代替
    image_url = models.URLField('图片URL', null=True, blank=True)
    created_at = models.DateTimeField('创建时间', auto_now_add=True)
    updated_at = models.DateTimeField('更新时间', auto_now=True)
    class Meta:
        verbose_name = '电影'
        verbose_name_plural = '电影'
        ordering = ['-rating']
        indexes = [
            models.Index(fields=['year', 'rating']), # 保留现有的复合索引
            models.Index(fields=['country']), # 添加国家索引
            models.Index(fields=['rating']), # 添加评分索引
            models.Index(fields=['created_at']), # 添加创建时间索引       
        ]
    @classmethod
    def get_word_cloud_data(cls):
        """
        获取电影简介词云数据，自动引用 stopwords.txt
        返回格式：[{name: '词语', value: 频次}, ...]
        """
        try:
            stop_words = load_stopwords()
            with connection.cursor() as cursor:
                cursor.execute("""
                    SELECT intro
                    FROM spider_movie
                    WHERE intro IS NOT NULL
                    AND intro != ''
                    AND intro != '暂无简介' -- 过滤掉"暂无简介"
                """)
                intros = cursor.fetchall()
                all_text = ' '.join([row[0] for row in intros])
                all_text = re.sub(r'[^\w\s]', '', all_text)
                words = jieba.lcut(all_text)
                filtered_words = [w for w in words if w not in stop_words and len(w) > 1]
                word_counts = Counter(filtered_words)
                most_common = word_counts.most_common(100)
                word_cloud_data = [
                    {'name': word, 'value': count}
                    for word, count in most_common
                ]
            return word_cloud_data
        except Exception as e:
            logger.error(f"获取词云数据失败: {str(e)}")
            return []

    def __str__(self):
        return f"{self.title} ({self.year})"
class RatingDistribution(models.Model):
    """评分分布模型"""
    rating = models.FloatField('评分')
    count = models.IntegerField('数量')
    class Meta:
        verbose_name = '评分分布'
        verbose_name_plural = '评分分布'
    def __str__(self):
        return f"评分 {self.rating}: {self.count}部"
# class MovieType(models.Model):
#     """电影类型模型"""
#     type_name = models.CharField('类型名称', max_length=50)
#     count = models.IntegerField('数量')
#     class Meta:
#         verbose_name = '电影类型'
#         verbose_name_plural = '电影类型'
#     def __str__(self):
#         return f"{self.type_name}: {self.count}部"
    
class MovieType(models.Model):
    """电影类型模型"""
    type_name = models.CharField('类型名称', max_length=50)
    count = models.IntegerField('数量')

    class Meta:
        verbose_name = '电影类型'
        verbose_name_plural = '电影类型'

    def __str__(self):
        return f"{self.type_name}: {self.count}部"

    @classmethod
    def get_type_distribution(cls):
        """
        获取电影类型分布数据
        返回格式：[{value: 数量, name: '类型名'}, ...]
        处理一部电影多个类型的情况，只返回前20种类型
        """
        try:
            with connection.cursor() as cursor:
                # SQL查询 - 使用子查询处理空格分隔的类型
                sql = """
                SELECT
                    genre as name,
                    COUNT(*) as value
                FROM (
                    SELECT
                        SUBSTRING_INDEX(SUBSTRING_INDEX(m.genre, ' ', numbers.n),
                        ' ', -1) genre
                    FROM
                        (SELECT 1 n UNION ALL
                        SELECT 2 UNION ALL
                        SELECT 3 UNION ALL
                        SELECT 4 UNION ALL
                        SELECT 5) numbers
                    INNER JOIN spider_movie m
                        ON CHAR_LENGTH(m.genre) - CHAR_LENGTH(REPLACE(m.genre, ' ',
                        '')) >= numbers.n - 1
                    WHERE m.genre != '' AND m.genre IS NOT NULL
                ) split_genres
                WHERE genre != ''
                GROUP BY genre
                ORDER BY value DESC, genre
                LIMIT 20;
                """
                cursor.execute(sql)
                results = cursor.fetchall()
                # 转换为所需格式
                type_distribution = [
                    {
                        "name": row[0],  # 类型名称
                        "value": int(row[1])  # 类型数量
                    }
                    for row in results
                ]
                return type_distribution
        except Exception as e:
            logger.error(f"获取电影类型分布失败: {str(e)}")
            return []

    @classmethod
    def get_type_count(cls):
        """获取电影类型总数"""
        try:
            with connection.cursor() as cursor:
                sql = """
                SELECT COUNT(DISTINCT genre)
                FROM (
                    SELECT
                        SUBSTRING_INDEX(SUBSTRING_INDEX(m.genre, ' ', numbers.n),
                        ' ', -1) genre
                    FROM
                        (SELECT 1 n UNION ALL
                        SELECT 2 UNION ALL
                        SELECT 3 UNION ALL
                        SELECT 4 UNION ALL
                        SELECT 5) numbers
                    INNER JOIN spider_movie m
                        ON CHAR_LENGTH(m.genre) - CHAR_LENGTH(REPLACE(m.genre, ' ',
                        '')) >= numbers.n - 1
                    WHERE m.genre != '' AND m.genre IS NOT NULL
                ) split_genres
                WHERE genre != '';
                """
                cursor.execute(sql)
                result = cursor.fetchone()
                return result[0] if result else 0
        except Exception as e:
            logger.error(f"获取电影类型总数失败: {str(e)}")
            return 0


    
# class YearDistribution(models.Model):
#     """年份分布模型"""
#     year = models.IntegerField('年份')
#     count = models.IntegerField('数量')
#     class Meta:
#         verbose_name = '年份分布'
#         verbose_name_plural = '年份分布'
#     def __str__(self):
#         return f"{self.year}年: {self.count}部"

# class CountryStats(models.Model):
#     """国家统计模型"""
#     country = models.CharField('国家', max_length=100, db_index=True)
#     count = models.IntegerField('数量')
#     avg_rating = models.FloatField('平均评分', null=True)
#     top_movie_title = models.CharField('代表作', max_length=200, null=True)
#     top_movie_rating = models.FloatField('代表作评分', null=True)
#     top_movie_year = models.IntegerField('代表作年份', null=True)
#     updated_at = models.DateTimeField('更新时间', auto_now=True)

#     class Meta:
#         verbose_name = '国家统计'
#         verbose_name_plural = '国家统计'
#         indexes = [
#             models.Index(fields=['country']),
#             models.Index(fields=['avg_rating']),
#         ]

#     def __str__(self):
#         return f"{self.country}: {self.count}部"
    
    # @classmethod
    # def update_country_stats(cls):
    #     """更新国家统计数据"""
    #     try:
    #         with transaction.atomic():
    #             # 清除旧数据
    #             cls.objects.all().delete()
    #             # 使用原生SQL查询优化性能
    #             with connection.cursor() as cursor:
    #                 cursor.execute("""
    #                     WITH country_stats AS (
    #                         -- 将电影按国家拆分，每个国家一行
    #                         SELECT
    #                             country,
    #                             COUNT(*) as count,
    #                             AVG(rating) as avg_rating,
    #                             MAX(rating) as max_rating
    #                         FROM spider_movie
    #                         WHERE country IS NOT NULL
    #                         GROUP BY country
    #                 ),
    #                 top_movies AS (
    #                     SELECT DISTINCT ON (country)
    #                         country,
    #                         title,
    #                         rating,
    #                         year
    #                     FROM spider_movie
    #                     WHERE country IS NOT NULL
    #                     ORDER BY country, rating DESC
    #                 )
    #                 SELECT
    #                     cs.country,
    #                     cs.count,
    #                     ROUND(cs.avg_rating::numeric, 1) as avg_rating,
    #                     tm.title as top_movie_title,
    #                     tm.rating as top_movie_rating,
    #                     tm.year as top_movie_year
    #                 FROM country_stats cs
    #                 LEFT JOIN top_movies tm ON cs.country = tm.country
    #                 ORDER BY cs.count DESC
    #             """)
    #             # 批量创建对象
    #             objects_to_create = []
    #             for row in cursor.fetchall():
    #                 objects_to_create.append(
    #                     cls(
    #                         country=row[0],
    #                         count=row[1],
    #                         avg_rating=float(row[2]) if row[2] else None,
    #                         top_movie_title=row[3],
    #                         top_movie_rating=float(row[4]) if row[4] else
    #                         None,
    #                         top_movie_year=row[5]
    #                     )
    #                 )
    #             # 批量创建
    #             cls.objects.bulk_create(objects_to_create, batch_size=1000)
    #         return True
    #     except Exception as e:
    #         logger.error(f"更新国家统计数据时出错: {str(e)}")
    #         return False

from .services import WorldMapDataService
@classmethod
def update_country_stats(cls):
    """更新国家统计数据"""
    try:
        with transaction.atomic():
            # 清除旧数据
            cls.objects.all().delete()
            print("已清除旧数据")

            # 使用原生SQL查询优化性能
            with connection.cursor() as cursor:
                # 主查询 - 使用空格分割国家
                cursor.execute("""
                    WITH movie_countries AS (
                        -- 将电影按国家拆分，每个国家一行
                        SELECT
                            m.id,
                            m.title,
                            m.rating,
                            m.year,
                            TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(
                                m.country, ' ', n.n), ' ', -1)) as country
                        FROM spider_movie m
                        CROSS JOIN (
                            SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
                        ) n
                        WHERE
                            m.country IS NOT NULL
                            AND m.country != ''
                            AND n.n <= 4
                            AND n.n <= 1 + (LENGTH(m.country) - LENGTH(REPLACE(m.country, ' ', '')))
                        ),
                        country_stats AS (
                            -- 计算每个国家的统计数据
                            SELECT
                                country,
                                COUNT(*) as count,
                                AVG(rating) as avg_rating,
                                MAX(rating) as max_rating
                            FROM movie_countries
                            WHERE country != ''
                            GROUP BY country
                        ),
                        top_movies AS (
                            -- 获取每个国家评分最高的电影
                            SELECT
                                mc1.country,
                                mc1.title,
                                mc1.rating,
                                mc1.year
                            FROM movie_countries mc1
                            INNER JOIN (
                                SELECT country, MAX(rating) as max_rating
                                FROM movie_countries
                                GROUP BY country
                            ) mc2 ON mc1.country = mc2.country AND mc1.rating = mc2.max_rating
                        )
                        SELECT
                            cs.country,
                            cs.count,
                            ROUND(cs.avg_rating, 1) as avg_rating,
                            tm.title as top_movie_title,
                            tm.rating as top_movie_rating,
                            tm.year as top_movie_year
                        FROM country_stats cs
                        LEFT JOIN top_movies tm ON cs.country = tm.country
                        ORDER BY cs.count DESC;
                """)

                # 批量创建对象（以下逻辑均属于 with connection.cursor() 代码块内部）
                objects_to_create = []
                rows = cursor.fetchall()
                print(f"\n=== 查询结果 ===")
                for row in rows:
                    print(f"国家: {row[0]}, 数量: {row[1]}, 平均分: {row[2]}, 代表作: {row[3]}")
                print("===============")

                # 用于合并相同国家的统计数据
                country_stats_dict = {}
                for row in rows:
                    # 标准化国家名称
                    country = WorldMapDataService._normalize_country_name(row[0])
                    if not country:  # 跳过无效的国家名
                        print(f"跳过无效的国家名: {row[0]}")
                        continue
                    # 如果这个国家已经存在，合并统计数据
                    if country in country_stats_dict:
                        existing = country_stats_dict[country]
                        # 更新计数
                        existing['count'] += row[1]
                        # 更新平均分（使用加权平均）
                        total_rating = existing['avg_rating'] * existing['count'] + float(row[2]) * row[1]
                        total_count = existing['count'] + row[1]
                        existing['avg_rating'] = round(total_rating / total_count, 1)
                        # 更新最高分电影
                        if float(row[4]) > existing['top_movie_rating']:
                            existing['top_movie_title'] = row[3]
                            existing['top_movie_rating'] = float(row[4])
                            existing['top_movie_year'] = row[5]
                    else:
                        # 创建新的国家统计
                        country_stats_dict[country] = {
                            'count': row[1],
                            'avg_rating': float(row[2]) if row[2] else None,
                            'top_movie_title': row[3],
                            'top_movie_rating': float(row[4]) if row[4] else None,
                            'top_movie_year': row[5]
                        }

                # 创建 CountryStats 对象
                for country, stats in country_stats_dict.items():
                    objects_to_create.append(
                        cls(
                            country=country,
                            count=stats['count'],
                            avg_rating=stats['avg_rating'],
                            top_movie_title=stats['top_movie_title'],
                            top_movie_rating=stats['top_movie_rating'],
                            top_movie_year=stats['top_movie_year']
                        )
                    )

                # 批量创建
                if objects_to_create:
                    cls.objects.bulk_create(objects_to_create, batch_size=1000)
                    print(f"\n=== 最终结果 ===")
                    for obj in objects_to_create:
                        print(f"国家: {obj.country}, 数量: {obj.count}, 平均分: {obj.avg_rating}, 代表作: {obj.top_movie_title}")
                    print("===============")
                else:
                    print("没有创建任何记录")

            # 清除缓存（在 transaction.atomic 代码块内，确保事务提交后清理缓存）
            WorldMapDataService.clear_cache()
            return True
    except Exception as e:
        print(f"更新国家统计数据时出错: {str(e)}")
        return False


class CrawlLog(models.Model):
    """爬虫日志模型"""
    STATUS_CHOICES = (
        ('running', '运行中'),
        ('success', '成功'),
        ('failed', '失败'),
    )
    start_time = models.DateTimeField('开始时间', auto_now_add=True)
    end_time = models.DateTimeField('结束时间', null=True, blank=True)
    status = models.CharField('状态', max_length=10, choices=STATUS_CHOICES)
    movie_count = models.IntegerField('电影数量', default=0)
    error_message = models.TextField('错误信息', blank=True)
    class Meta:
        verbose_name = '爬虫日志'
        verbose_name_plural = '爬虫日志'
        ordering = ['-start_time']
    def __str__(self):
        return f"爬虫任务 {self.start_time.strftime('%Y-%m-%d %H:%M:%S')}"
    
class YearDistribution(models.Model):
    """年份分布模型"""
    year = models.IntegerField('年份')
    count = models.IntegerField('数量')
    class Meta:
        verbose_name = '年份分布'
        verbose_name_plural = '年份分布'
    def __str__(self):
        return f"{self.year}年: {self.count}部"
    @classmethod
    def update_year_distribution(cls):
        try:
            # 使用批量操作
            with transaction.atomic():
                # 清除旧数据
                cls.objects.all().delete()
                # 使用 bulk_create 批量创建
                year_data = Movie.objects.exclude(
                year__isnull=True
                ).exclude(
                year=''
                ).values('year').annotate(
                count=Count('id')
                ).order_by('year')
                # 批量创建对象
                objects_to_create = []
                for item in year_data:
                    year_str = str(item['year']).strip()
                    if re.fullmatch(r'\d{4}', year_str):
                        year_int = int(year_str)
                        if 1900 <= year_int <= 2100:
                            objects_to_create.append(
                                cls(year=year_int, count=item['count'])
                            )
                # 批量创建
                cls.objects.bulk_create(objects_to_create, batch_size=1000)
            return True
        except Exception as e:
            logger.error(f"更新年份分布数据时出错: {str(e)}")
            return False

# from django.db import connection
# class Movie(models.Model):
#     # ... 其他字段保持不变 ...
#     class Meta:
#         verbose_name = '电影'
#         verbose_name_plural = '电影'
#         ordering = ['-rating']
#         indexes = [
#             models.Index(fields=['year', 'rating']), # 保留现有的复合索引
#             models.Index(fields=['country']), # 添加国家索引
#             models.Index(fields=['rating']), # 添加评分索引
#             models.Index(fields=['created_at']), # 添加创建时间索引
#     ]
#修改 CountryStats 模型，添加更多统计字段
class CountryStats(models.Model):
    """国家统计模型"""
    country = models.CharField('国家', max_length=100, db_index=True)
    count = models.IntegerField('数量')
    avg_rating = models.FloatField('平均评分', null=True)
    top_movie_title = models.CharField('代表作', max_length=200, null=True)
    top_movie_rating = models.FloatField('代表作评分', null=True)
    top_movie_year = models.IntegerField('代表作年份', null=True)
    updated_at = models.DateTimeField('更新时间', auto_now=True)
    class Meta:
        verbose_name = '国家统计'
        verbose_name_plural = '国家统计'
        indexes = [
            models.Index(fields=['country']),
            models.Index(fields=['avg_rating']),
        ]
    def __str__(self):
        return f"{self.country}: {self.count}部"
    @classmethod
    def update_country_stats(cls):
        """更新国家统计数据"""
        try:
            with transaction.atomic():
                # 清除旧数据
                cls.objects.all().delete()
                # 使用原生SQL查询优化性能
                with connection.cursor() as cursor:
                    cursor.execute("""
                        WITH country_stats AS (
                        SELECT
                            country,
                            COUNT(*) as count,
                            AVG(rating) as avg_rating,
                            MAX(rating) as max_rating
                        FROM spider_movie
                        WHERE country IS NOT NULL
                        GROUP BY country
                    ),
                    top_movies AS (
                    SELECT DISTINCT ON (country)
                        country,
                        title,
                        rating,
                        year
                    FROM spider_movie
                    WHERE country IS NOT NULL
                    ORDER BY country, rating DESC
                    )
                    SELECT
                        cs.country,
                        cs.count,
                        ROUND(cs.avg_rating::numeric, 1) as avg_rating,
                        tm.title as top_movie_title,
                        tm.rating as top_movie_rating,
                        tm.year as top_movie_year
                    FROM country_stats cs
                    LEFT JOIN top_movies tm ON cs.country = tm.country
                    ORDER BY cs.count DESC
                """)
                # 批量创建对象
                objects_to_create = []
                for row in cursor.fetchall():
                    objects_to_create.append(
                        cls(
                            country=row[0],
                            count=row[1],
                            avg_rating=float(row[2]) if row[2] else None,
                            top_movie_title=row[3],
                            top_movie_rating=float(row[4]) if row[4] else None,
                            top_movie_year=row[5]
                        )
                    )
                # 批量创建
                cls.objects.bulk_create(objects_to_create, batch_size=1000)
            return True
        except Exception as e:
            logger.error(f"更新国家统计数据时出错: {str(e)}")
            return False

import os
import jieba
from collections import Counter

def load_stopwords():
    stopwords_path = os.path.join(os.path.dirname(__file__), 'stopwords.txt')
    stop_words = set()
    try:
        with open(stopwords_path, 'r', encoding='utf-8') as f:
            for line in f:
                word = line.strip()
                if word:
                    stop_words.add(word)
    except Exception as e:
        logger.warning(f"加载停用词失败: {e}")
    return stop_words

# class Movie(models.Model):
#     # ... 你的其他字段 ...
